package com.hongsheng.mapper;/*
@author:刘逸思普
@Date:2023/8/10
 */

import com.baomidou.mybatisplus.core.conditions.Wrapper;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.hongsheng.pojo.*;
import com.hongsheng.pojo.Package;
import com.hongsheng.pojo.vo.OrderReportVo;
import com.hongsheng.util.OrderSqlProvider;
import org.apache.ibatis.annotations.*;

import java.util.Date;
import java.util.List;
import java.util.Map;
import java.util.Scanner;

@Mapper
public interface OrderMapper extends BaseMapper<Order> {
    @Select("select * from tb_order")
    @Results({
            @Result(id = true,property = "id",column = "id"),
            @Result(property = "customer",column = "c_id",javaType = Customer.class,
                    one = @One(select = "com.hongsheng.mapper.OrderMapper.findCustomerByOid")),
            @Result(property = "line",column = "l_id",javaType = Line.class,
                    one = @One(select = "com.hongsheng.mapper.OrderMapper.findLineById")),
            @Result(property = "goodsList",column = "o_id",javaType = List.class,
                    many = @Many(select = "com.hongsheng.mapper.OrderMapper.findGoodsByOid")),
            @Result(property = "drivers",column = "id",javaType = List.class,
                    many = @Many(select = "com.hongsheng.mapper.OrderMapper.findDriverByOid")),
            @Result(property = "amount",column = "amount"),
            @Result(property = "orderStatus",column = "order_status"),
            @Result(property = "createTime",column = "create_time"),
            @Result(property = "sendTime",column = "send_time"),
    })
//    List<Order> findOrderList(@Param("id") Integer id,@Param("cId")Integer cID,@Param("lId")Integer lId,@Param("orderStatus")Integer orderStatus);
//    @Param("id") Integer id,@Param("cId")Integer cID,@Param("lId")Integer lId,@Param("orderStatus")Integer orderStatus
    List<Order> findOrderList();
    @SelectProvider(type = OrderSqlProvider.class,method = "getOrder")
    @Results({
            @Result(id = true,property = "id",column = "id"),
            @Result(property = "customer",column = "c_id",javaType = Customer.class,
                    one = @One(select = "com.hongsheng.mapper.OrderMapper.findCustomerByOid")),
            @Result(property = "line",column = "l_id",javaType = Line.class,
                    one = @One(select = "com.hongsheng.mapper.OrderMapper.findLineById")),
            @Result(property = "goodsList",column = "o_id",javaType = List.class,
                    many = @Many(select = "com.hongsheng.mapper.OrderMapper.findGoodsByOid")),
            @Result(property = "drivers",column = "id",javaType = List.class,
                    many = @Many(select = "com.hongsheng.mapper.OrderMapper.findDriverByOid")),
            @Result(property = "amount",column = "amount"),
            @Result(property = "orderStatus",column = "order_status"),
            @Result(property = "createTime",column = "create_time"),
            @Result(property = "sendTime",column = "send_time"),
    })
//    List<Order> findOrderList(
    List<Order> findOrderPage(@Param("order") Order order);

    @Select("select * from tb_customer where id=#{oId}")
    Customer findCustomerByOid();

    @Select("select DISTINCT dr.* from tb_driver dr join tb_goods tg on tg.dr_id = dr.id where tg.o_id=#{id}")
    @Results({
            @Result(id = true,property = "id",column = "id"),
            @Result(property = "driverName",column = "driver_name"),
            @Result(property = "driverPhone",column = "driver_phone"),
            @Result(property = "type",column = "type"),
            @Result(property = "carLoad",column = "car_load"),
            @Result(property = "size",column = "size"),
            @Result(property = "salary",column = "salary"),
            @Result(property = "status",column = "status"),
            @Result(property = "address",column = "address"),
            @Result(property = "line",column = "l_id",javaType = Line.class,
                    one=@One(select = "com.hongsheng.mapper.DriverMapper.findByIdLine"))
    })
    List<Driver> findDriverByOid(Integer id);


    @Select("select * from tb_line where id=#{id}")
    Line findLineById(Integer id);


    @Update("update tb_order set  amount=#{amount} where id=#{id}")
    Integer setAmount(@Param("amount") Double amount,@Param("id") Integer id);

    @Insert("insert into tb_order values (null,#{customer.id},#{line.id},0.0,#{orderStatus},NOW(),null)")
    public int addOrder(Order order);


    @Update("update tb_order set l_id=#{line.id},order_status=#{orderStatus},send_time=#{sendTime} where id=#{id}")
    public int updateOrder(Order order);

    @Delete("delete from tb_order where id=#{id}")
    public int deleteOrder(Integer id);

@Select("select * from tb_order where id=#{id}")
@Results({
        @Result(id = true,property = "id",column = "id"),
        @Result(property = "customer",column = "c_id",javaType = Customer.class,
                one = @One(select = "com.hongsheng.mapper.OrderMapper.findCustomerByOid")),
        @Result(property = "line",column = "l_id",javaType = Line.class,
                one = @One(select = "com.hongsheng.mapper.OrderMapper.findLineById")),
        @Result(property = "goodsList",column = "id",javaType = List.class,
                many = @Many(select = "com.hongsheng.mapper.OrderMapper.findGoodsById")),
        @Result(property = "drivers",column = "id",javaType = List.class,
                many = @Many(select = "com.hongsheng.mapper.OrderMapper.findDriverByOid")),
        @Result(property = "amount",column = "amount"),
        @Result(property = "orderStatus",column = "order_status"),
        @Result(property = "createTime",column = "create_time"),
        @Result(property = "sendTime",column = "send_time"),
})
    Order findOrderById(Integer id);



    @Select("select * from tb_goods where o_id=#{id}")
    @Results({
            @Result(property = "order_id",column = "o_id"),
            @Result(property = "depository",column = "de_id", javaType = Depository.class,
                    one=@One(select =
                            "com.hongsheng.mapper.GoodsMapper.findDepositoryById")),
            @Result(property = "driver",column = "dr_id", javaType = Driver.class,
                    one=@One(select =
                            "com.hongsheng.mapper.DriverMapper.findDriverById")),
            @Result(property = "pack",column = "p_id", javaType = Package.class,
                    one=@One(select =
                            "com.hongsheng.mapper.GoodsMapper.findPackageById")),
            @Result(property = "name",column = "name"),
            @Result(property = "type",column = "type"),
            @Result(property = "weight",column = "weight"),
            @Result(property = "size",column = "size"),
            @Result(property = "quantity",column = "quantity"),
            @Result(property = "nature",column = "nature")
    })
    List<Goods> findGoodsById(int id);

    @Select("SELECT SUM(size * quantity) AS total_volume FROM tb_goods where de_id=#{id}")
    Double capacity(Integer id);
    @Select("SELECT SUM(tg.size * tg.quantity) AS total_volume FROM tb_goods tg join tb_driver td   where tg.dr_id=#{id} and td.status!=0")
    Double totalSize(Integer id);
    @Select("SELECT SUM(size * quantity) AS total_volume FROM tb_goods where o_id=#{id}")
    Double totalOrderSize(Integer id);
    @Select("SELECT SUM(weight * quantity) AS total_volume FROM tb_goods where o_id=#{id}")
    Double totalOrderWeight(Integer id);
    @Select("SELECT SUM(tg.weight * tg.quantity) AS total_volume FROM tb_goods tg join tb_driver td  where tg.dr_id=#{id} and td.status!=0")
    Double totalWeight(Integer id);
    @Delete("UPDATE tb_driver SET status = 0 WHERE id NOT IN (SELECT DISTINCT dr_id FROM tb_goods)")
    Integer driverStu();

    @Delete("delete from tb_goods where o_id=#{id}")
    Integer delGoodsByOid(Integer id);
@Select("SELECT DISTINCT address FROM tb_depository")
    List<String> findStartAddress();

    /***
     *  根据月份查询订单数量
     * @return
     */
    @Select("SELECT count( 1 ) AS order_number,MONTH ( send_time ) AS `month` FROM `tb_order` WHERE order_status = 4 and year(send_time)=2023 GROUP BY MONTH (send_time) ORDER BY `month`")
    @Results({
            @Result(property = "value",column = "order_number"),
            @Result(property = "date",column = "month")
    })
    List<OrderReportVo>  selectOrderByMonth();


    @Update("update tb_driver set address=#{address} where status=0")
    Integer lisStu(String address);


    @Select("select DISTINCT address from tb_depository")
    List<String> AllAddress();

    @Select("select count(id) from tb_order")
    Integer getOrderCount();
    @Select("select sum(amount) from tb_bill where type='收入' ")
    Double getBillIn();
    @Select("select count(id) from tb_customer")
    Integer getCustomer();
    @Select("select sum(amount) from tb_bill where type='支出' ")
    Double getBillOut();
}
